﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace MyVideoTek
{
    public class SqlDataAbstract
    {
        public DataTable GetListFilms()
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();

            DataTable resultTable = db.Select("SELECT * FROM film ORDER BY titre_film ASC");

            //Recupère la ligne de resultat
            return resultTable;
        }

        public DataTable GetListGenre()
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();

            DataTable resultTable = db.Select("SELECT nom_genre as [Genre] FROM genre ORDER BY nom_genre ASC");

            //Recupère la ligne de resultat
            return resultTable;
        }

        public DataTable SelectIdGenreFilmByIdGenre(string idGenre)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();
            DataTable resultTable = db.Select("SELECT * FROM idgenre WHERE id_genre = '" + idGenre + "'");

            return resultTable;
        }

        public DataTable GetFilmByIdGenre(string idGenre)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();
            DataTable resultTable = db.Select("SELECT * FROM film WHERE id_genre = '" + idGenre + "'");

            return resultTable;
        }

        public DataTable GetFilmById(string idFilm)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();
            DataTable resultTable = db.Select("SELECT * FROM film WHERE id_film = '" + idFilm + "'");

            return resultTable;
        }

        public DataTable GetFilmByNomGenre(string nomGenre)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();
            DataTable resultTable = db.Select("SELECT titre_film, affiche_film FROM film f, idgenre i , genre g " + 
                                              " WHERE g.id_genre = i.id_genre " +
                                              " AND i.id_film = f.id_film " +
                                              " AND g.nom_genre = '" + nomGenre + "'");


            return resultTable;
        }

        public DataTable GetGenreNameByIdFilm(string idFilm)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();
            DataTable resultTable = db.Select("SELECT g.nom_genre FROM film f, idgenre i , genre g " + 
                                              " WHERE g.id_genre = i.id_genre  " +
                                              " AND i.id_film = f.id_film  " +
                                              " AND i.id_film = '" + idFilm + "'");

            return resultTable;
        }


        public DataTable GetFilmSearch(string searchTitre)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();
            DataTable resultTable = db.Select("SELECT titre_film, affiche_film FROM film " +
                                              " WHERE film.titre_film like '%" + searchTitre + "%'");

            return resultTable;
        }

        public void DeleteIdGenreByIdFilm(int idFilm)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();

            db.Insert("DELETE FROM idgenre WHERE id_film = '" + idFilm + "'");
        }

        public void DeleteFilmById(int idFilm)
        {
            //Connection à la base de donnée
            SqliteData db = new SqliteData();

            db.Insert("DELETE FROM film WHERE id_film = '" + idFilm + "'");
            db.Insert("DELETE FROM fichier WHERE id_film = '" + idFilm + "'");
            db.Insert("DELETE FROM idgenre WHERE id_film = '" + idFilm + "'");
        }
    }
}






